#!/usr/bin/perl

use strict;
use XML::LibXML;
use DBI;
use Config::IniFiles;
use Time::Local;
use HTTP::Date;

########################################################
####config specific
#config
my $cfg=Config::IniFiles->new( -file => '/var/myscheduler/dbConfig.ini');
my $db_name="rcoos";
my $tname=$cfg->val('rcoos','tname');
my $db_user=$cfg->val('rcoos','username');
my $db_passwd=$cfg->val('rcoos','password');


#daylight savings time consideration
my ($temp_sec,$temp_min,$temp_hour,$temp_mday,$temp_mon,$temp_year,$temp_wday,$temp_yday,$isdst) = localtime(time);
my $dsttime;
if($isdst==0){
    $dsttime=5;
}
if($isdst==1){
    $dsttime=4;
}

#establish database connection
my ($dbh,$sth,$sql);
my $dbh = DBI->connect ( "dbi:Pg:dbname=$db_name", $db_user, $db_passwd);
if ( !defined $dbh ) {
       die "Cannot connect to database!\n";
       exit 0;
} 

  $sql = qq{ select platform.short_name,platform_handle,fixed_longitude,fixed_latitude from platform,organization where platform.organization_id = organization.row_id and organization.short_name='nerrs' };

   $sth = $dbh->prepare( $sql );
   $sth->execute();
   my (@platform,@long,@lat,@plathandle);
   while (my @row = $sth->fetchrow_array()) {
      push(@platform,$row[0]);
      push(@plathandle,$row[1]);
      push(@long,$row[2]);
      push(@lat,$row[3]);
   }


   my %mtype_lookup;
   
    my $sql="select platform.short_name,obs_type.standard_name,sensor.row_id,m_type.row_id,sensor.fixed_z from sensor
            left join m_type on m_type.row_id=sensor.m_type_id
            left join m_scalar_type on m_type.m_scalar_type_id=m_scalar_type.row_id
            left join obs_type on obs_type.row_id=m_scalar_type.obs_type_id
            left join platform on platform.row_id=sensor.platform_id
            left join organization on organization.row_id=platform.organization_id
            where organization.short_name='nerrs'"; 

    my $sth = $dbh->prepare($sql);
    $sth->execute();
                     
    while(my @row = $sth->fetchrow_array()){
         #print "**$row[0]&&\t**$row[1]&&\t**$row[2]&&\t**$row[3]&&\t**$row[4]**\n";
         if((!$row[4])||($row[4] eq "")||($row[4] eq " ")){
             $row[4]="NULL";
         }
         $mtype_lookup{platform}{$row[0]}{obs_list}{$row[1]}{sensor_id}=$row[2];
         $mtype_lookup{platform}{$row[0]}{obs_list}{$row[1]}{mtype_id}=$row[3];
         $mtype_lookup{platform}{$row[0]}{obs_list}{$row[1]}{m_z}=$row[4];

    }



########################################################
#process platforms same one by one, xml->sql->database

for(my $i=0;$i<@platform;$i++) {

   my $sqlstring='';
   my $filename = '/tmp/nerrs/cdmo_'.$platform[$i];
   
   ########################################################
   #process the clean xml document into SQL statements

   my $xp = XML::LibXML->new->parse_file($filename.'.xml');

   my $line_count = 0;
   foreach my $row ($xp->findnodes('//data/r')) {
	$line_count++;
	if ($line_count < 2) { next; }

	#print "###################\n";

        #possible vars used
        my @obs = ();
        my $m_date;
	my ($row_id,$historical);
        my ($air_temp,$air_temp_qc,$rh,$rh_qc,$air_pressure,$air_pressure_qc,$wind_speed,$wind_speed_qc,$wind_from_direction,$wind_from_direction_qc,$precipitation,$precipitation_qc,$solar,$solar_qc);
        my ($water_temp,$water_temp_qc,$water_conductivity,$water_conductivity_qc,$salinity,$salinity_qc,$do_percent,$do_percent_qc,$do_mgl,$do_mgl_qc,$water_level,$water_level_qc,$ph,$ph_qc,$turbidity,$turbidity_qc);

	#push attribute values into array and then array into correct variables
	my @values = ();
	foreach my $element ($row->findnodes('c/@v')) { push (@values, $element->string_value()); }

           if(@values<3) {next;}

           ##file types
           if (substr($platform[$i],-3) eq 'met') {
                ($row_id,$historical,$m_date,$air_temp,$air_temp_qc,$rh,$rh_qc,$air_pressure,$air_pressure_qc,$wind_speed,$wind_speed_qc,$wind_from_direction,$wind_from_direction_qc,$precipitation,$precipitation_qc,$solar,$solar_qc) = @values;
		#print "$air_temp\n";
                @obs = ("air_temperature",$air_temp,$air_temp_qc,"relative_humidity",$rh,$rh_qc,"air_pressure",$air_pressure,$air_pressure_qc,"wind_speed",$wind_speed,$wind_speed_qc,"wind_from_direction",$wind_from_direction,$wind_from_direction_qc,"precipitation",$precipitation,$precipitation_qc,"solar_radiation",$solar,$solar_qc);
           }
           if (substr($platform[$i],-2) eq 'wq') {
		($row_id,$historical,$m_date,$water_temp,$water_temp_qc,$water_conductivity,$water_conductivity_qc,$salinity,$salinity_qc,$do_percent,$do_percent_qc,$do_mgl,$do_mgl_qc,$water_level,$water_level_qc,$ph,$ph_qc,$turbidity,$turbidity_qc) = @values;
		#print "$water_temp\n";
        	@obs = ("water_temperature",$water_temp,$water_temp_qc,"water_conductivity",$water_conductivity,$water_conductivity_qc,"salinity",$salinity,$salinity_qc,"oxygen_saturation",$do_percent,$do_percent_qc,"oxygen_concentration",$do_mgl,$do_mgl_qc,"gage_height",$water_level,$water_level_qc,"ph",$ph,$ph_qc,"turbidity",$turbidity,$turbidity_qc);
           }

                      
           $m_date = substr($m_date,6,4).'-'.substr($m_date,0,2).'-'.substr($m_date,3,2).' '.substr($m_date,11,5).':00';
          
           $m_date=get_gmt_time($m_date,$dsttime);

           while (@obs) {

                my $sensor_id = shift(@obs);
                my $m_value = shift(@obs);
                my $qc_level = shift(@obs);
         #       print "**$platform[$i]&&\t**$sensor_id**\t**$m_value**\t";
           
                if($m_value ne "null"){
                my %s=%{$mtype_lookup{platform}{$platform[$i]}{obs_list}{$sensor_id}};

                if(defined(%s)){
                    my $sensor_id=$s{sensor_id};
                    my $m_type_id=$s{mtype_id};
                    my $m_z=$s{m_z};

                    #print "**$m_date**\t$sensor_id\t$m_type_id\t$m_z\n";
                    
                    $sqlstring="INSERT INTO $tname (row_entry_date,row_update_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value,qc_level) VALUES (current_timestamp,current_timestamp,'$plathandle[$i]',$sensor_id,$m_type_id,'$m_date',$long[$i],$lat[$i],$m_z,$m_value,$qc_level)";
#print $sqlstring."\n";                    
                    $sth = $dbh->prepare($sqlstring);
                    $sth->execute();
                    
                 }
               }
           } #while @obs

   } #foreach $row

  #print $sqlstring;
# system("psql rcoos -U rcoosx -c $sqlstring");

} #while (@platform) {

$sth->finish;
$dbh->disconnect();

sub get_gmt_time{
 my ($dat,$dsttime)=@_;
 
 my $t=str2time($dat);
 my ($sec,$min,$hour,$mday,$mon,$year,$twday,$yday,$isdst) = localtime($t+$dsttime*3600);
     $year=$year+1900;
     $mon=$mon+1;
     if($mon<10){
        $mon="0".$mon;
     }
    if($mday<10){
       $mday="0".$mday;
    }
    if($sec<10){
       $sec="0".$sec;
    }
   if($min<10){
     $min="0".$min;
   }
   if($hour<10){
     $hour="0".$hour;
   }
   $dat=$year."-".$mon."-".$mday." ".$hour.":".$min.":".$sec;
   
   return $dat;
}
